package com.example.drcbackend.service;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.example.drcbackend.Mapper.SubsetdbMapper;
import com.example.drcbackend.entity.Subsetdb;
import com.example.drcbackend.entity.functionaldependencies;
import com.example.drcbackend.Mapper.functionaldependenciesMapper;
import com.example.drcbackend.util.SqlParserUtil;
import com.example.drcbackend.entity.dto.TableSchema;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

@Service
@Transactional
public class ConsistencyService {

    private static final Logger logger = LoggerFactory.getLogger(ConsistencyService.class);

    @Autowired
    private functionaldependenciesMapper fm;
    @Autowired
    private SubsetdbMapper sm;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public boolean checkConsistency(int DBid, List<String> logs) throws IOException, SQLException {
        String tableName = null;
        try {
            // 1. 读取SQL文件内容
            String filePath = "E:/SQL_test/" + DBid + ".sql";
            List<String> sqlLines = Files.readAllLines(Paths.get(filePath), StandardCharsets.UTF_8);
            logs.add("读取SQL文件成功: " + filePath);
            logger.info("读取SQL文件成功: {}", filePath);

            // 2. 解析并执行SQL文件
            executeSqlLines(sqlLines, logs);

            // 3. 解析表结构
            List<TableSchema> schemas = SqlParserUtil.extractAllSchemas(String.join("\n", sqlLines));
            if (schemas.isEmpty()) {
                throw new IllegalArgumentException("SQL文件未包含有效表结构");
            }
            TableSchema schema = schemas.get(0);
            tableName = schema.getName();
            List<String> columns = schema.getAttributes();
            logs.add("解析表结构成功: 表名=" + tableName + ", 列名=" + columns);
            logger.info("解析表结构成功: 表名={}, 列名={}", tableName, columns);

            // 4. 查询函数依赖集
            QueryWrapper<functionaldependencies> lqw = new QueryWrapper<>();
            lqw.eq("dbid", DBid);
            List<functionaldependencies> fds = fm.selectList(lqw);
            logs.add("查询函数依赖集成功: dbid=" + DBid + ", 依赖数量=" + fds.size());
            logger.info("查询函数依赖集成功: dbid={}, 依赖数量={}", DBid, fds.size());

            // 5. 遍历函数依赖并验证
            for (functionaldependencies fd : fds) {
                String lhs = fd.getLhs().trim();
                String rhs = fd.getRhs().trim();
                logs.add("验证函数依赖: lhs=" + lhs + ", rhs=" + rhs);
                logger.info("验证函数依赖: lhs={}, rhs={}", lhs, rhs);

                // 检查列是否存在
                if (!validateColumnsExist(columns, lhs, rhs)) {
                    throw new IllegalArgumentException("函数依赖列不存在于表中: lhs=" + lhs + ", rhs=" + rhs);
                }

                // 构建验证SQL
                String validationSql = buildValidationQuery(tableName, lhs, rhs);
                logs.add("生成的验证SQL: " + validationSql);
                logger.info("生成的验证SQL: {}", validationSql);

                // 执行验证
                List<Map<String, Object>> violations = jdbcTemplate.queryForList(validationSql);
                if (!violations.isEmpty()) {
                    logs.add("发现违反函数依赖的数据: " + violations);
                    logger.warn("发现违反函数依赖的数据: {}", violations);
                    deleteTable(tableName, logs);
                    return false;
                }
            }

            deleteTable(tableName, logs); // 清理表
            logs.add("一致性检查结果: true");
            return true;
        } catch (Exception e) {
            logs.add("错误: " + e.getMessage());
            logger.error("一致性检测失败: DBid={}", DBid, e);
            if (tableName != null) deleteTable(tableName, logs);
            throw e;
        }
    }
    public boolean checksubConsistency(int DBid, List<String> logs) throws IOException, SQLException {
        //判断子集是否一致的方法
        String tableName = null;
        try {
            // 1. 读取SQL文件内容
            String filePath = "E:/SQL_test/" + DBid + ".sql";
            List<String> sqlLines = Files.readAllLines(Paths.get(filePath), StandardCharsets.UTF_8);
            logs.add("读取SQL文件成功: " + filePath);
            logger.info("读取SQL文件成功: {}", filePath);

            // 2. 解析并执行SQL文件
            executeSqlLines(sqlLines, logs);

            // 3. 解析表结构
            List<TableSchema> schemas = SqlParserUtil.extractAllSchemas(String.join("\n", sqlLines));
            if (schemas.isEmpty()) {
                throw new IllegalArgumentException("SQL文件未包含有效表结构");
            }
            TableSchema schema = schemas.get(0);
            tableName = schema.getName();
            List<String> columns = schema.getAttributes();
            logs.add("解析表结构成功: 表名=" + tableName + ", 列名=" + columns);
            logger.info("解析表结构成功: 表名={}, 列名={}", tableName, columns);

            // 4. 查询函数依赖集（与原sql文件不同，这里使用父文件查询函数依赖集）
            int fatherid=0;
            LambdaQueryWrapper<Subsetdb> lqwst = new LambdaQueryWrapper<>();
            lqwst.eq(Subsetdb::getSetdbid,DBid);
            Subsetdb setdb=sm.selectOne(lqwst);
            fatherid=setdb.getDbid();

            QueryWrapper<functionaldependencies> lqw = new QueryWrapper<>();

            lqw.eq("dbid", fatherid);
            List<functionaldependencies> fds = fm.selectList(lqw);
            logs.add("查询函数依赖集成功: dbid=" + DBid + ", 依赖数量=" + fds.size());
            logger.info("查询函数依赖集成功: dbid={}, 依赖数量={}", DBid, fds.size());

            // 5. 遍历函数依赖并验证
            for (functionaldependencies fd : fds) {
                String lhs = fd.getLhs().trim();
                String rhs = fd.getRhs().trim();
                logs.add("验证函数依赖: lhs=" + lhs + ", rhs=" + rhs);
                logger.info("验证函数依赖: lhs={}, rhs={}", lhs, rhs);

                // 检查列是否存在
                if (!validateColumnsExist(columns, lhs, rhs)) {
                    throw new IllegalArgumentException("函数依赖列不存在于表中: lhs=" + lhs + ", rhs=" + rhs);
                }

                // 构建验证SQL
                String validationSql = buildValidationQuery(tableName, lhs, rhs);
                logs.add("生成的验证SQL: " + validationSql);
                logger.info("生成的验证SQL: {}", validationSql);

                // 执行验证
                List<Map<String, Object>> violations = jdbcTemplate.queryForList(validationSql);
                if (!violations.isEmpty()) {
                    logs.add("发现违反函数依赖的数据: " + violations);
                    logger.warn("发现违反函数依赖的数据: {}", violations);
                    deleteTable(tableName, logs);
                    return false;
                }
            }

            deleteTable(tableName, logs); // 清理表
            logs.add("一致性检查结果: true");
            return true;
        } catch (Exception e) {
            logs.add("错误: " + e.getMessage());
            logger.error("一致性检测失败: DBid={}", DBid, e);
            if (tableName != null) deleteTable(tableName, logs);
            throw e;
        }
    }
    // 新增函数：根据SQL数据流判断数据库一致性
    public boolean checkSqlConsistency(List<String> sqlLines, int DBid, List<String> logs) throws SQLException {
        String tableName = null;
        try {
            // 1. 解析并执行SQL文件
            executeSqlLines(sqlLines, logs);

            // 2. 解析表结构
            List<TableSchema> schemas = SqlParserUtil.extractAllSchemas(String.join("\n", sqlLines));
            if (schemas.isEmpty()) {
                throw new IllegalArgumentException("SQL数据流未包含有效表结构");
            }
            TableSchema schema = schemas.get(0);
            tableName = schema.getName();
            List<String> columns = schema.getAttributes();
            logs.add("解析表结构成功: 表名=" + tableName + ", 列名=" + columns);
            logger.info("解析表结构成功: 表名={}, 列名={}", tableName, columns);

            // 3. 查询函数依赖集
            QueryWrapper<functionaldependencies> lqw = new QueryWrapper<>();
            lqw.eq("dbid", DBid);
            List<functionaldependencies> fds = fm.selectList(lqw);
            logs.add("查询函数依赖集成功: dbid=" + DBid + ", 依赖数量=" + fds.size());
            logger.info("查询函数依赖集成功: dbid={}, 依赖数量={}", DBid, fds.size());

            // 4. 遍历函数依赖并验证
            for (functionaldependencies fd : fds) {
                String lhs = fd.getLhs().trim();
                String rhs = fd.getRhs().trim();
                logs.add("验证函数依赖: lhs=" + lhs + ", rhs=" + rhs);
                logger.info("验证函数依赖: lhs={}, rhs={}", lhs, rhs);

                // 检查列是否存在
                if (!validateColumnsExist(columns, lhs, rhs)) {
                    throw new IllegalArgumentException("函数依赖列不存在于表中: lhs=" + lhs + ", rhs=" + rhs);
                }

                // 构建验证SQL
                String validationSql = buildValidationQuery(tableName, lhs, rhs);
                logs.add("生成的验证SQL: " + validationSql);
                logger.info("生成的验证SQL: {}", validationSql);

                // 执行验证
                List<Map<String, Object>> violations = jdbcTemplate.queryForList(validationSql);
                if (!violations.isEmpty()) {
                    logs.add("发现违反函数依赖的数据: " + violations);
                    logger.warn("发现违反函数依赖的数据: {}", violations);
                    deleteTable(tableName, logs);
                    return false;
                }
            }

            deleteTable(tableName, logs); // 清理表
            logs.add("一致性检查结果: true");
            return true;
        } catch (Exception e) {
            logs.add("错误: " + e.getMessage());
            logger.error("一致性检测失败: DBid={}", DBid, e);
            if (tableName != null) deleteTable(tableName, logs);
            throw e;
        }
    }
    // 执行SQL语句
    private void executeSqlLines(List<String> sqlLines, List<String> logs) throws SQLException {
        StringBuilder ddlAndDml = new StringBuilder();
        boolean isComment = false;

        for (String line : sqlLines) {
            line = line.trim();
            if (line.startsWith("/*")) {
                isComment = true;
            } else if (line.endsWith("*/")) {
                isComment = false;
                continue;
            }
            if (isComment || line.isEmpty() || line.startsWith("--")) continue;
            ddlAndDml.append(line).append("\n");
        }

        String[] sqlStatements = ddlAndDml.toString().split(";");
        try (Connection connection = jdbcTemplate.getDataSource().getConnection();
             Statement statement = connection.createStatement()) {
            connection.setAutoCommit(false);
            for (String sql : sqlStatements) {
                if (sql.trim().isEmpty()) continue;
                statement.execute(sql);
                logs.add("执行SQL成功: " + sql);
            }
            connection.commit();
        } catch (SQLException e) {
            logs.add("SQL执行失败: " + e.getMessage());
            logger.error("SQL执行失败", e);
            throw e;
        }
    }

    // 列名验证
    private boolean validateColumnsExist(List<String> columns, String lhs, String rhs) {
        List<String> lhsColumns = Arrays.asList(lhs.split("\\s*,\\s*"));
        return columns.containsAll(lhsColumns) && columns.contains(rhs);
    }

    // 构建验证SQL
    private String buildValidationQuery(String tableName, String lhs, String rhs) {
        String escapedLhs = Arrays.stream(lhs.split("\\s*,\\s*"))
                .map(column -> "`" + column + "`")
                .reduce((a, b) -> a + ", " + b)
                .orElseThrow(() -> new IllegalArgumentException("左部列不能为空"));
        String escapedRhs = "`" + rhs + "`";
        return String.format(
                "SELECT %s, COUNT(DISTINCT %s) AS unique_count FROM `%s` GROUP BY %s HAVING unique_count > 1",
                escapedLhs, escapedRhs, tableName, escapedLhs
        );
    }

    // 清理表
    private void deleteTable(String tableName, List<String> logs) {
        try {
            jdbcTemplate.execute("DROP TABLE IF EXISTS `" + tableName + "`");
            logs.add("表删除成功: " + tableName);
            logger.info("表删除成功: {}", tableName);
        } catch (Exception e) {
            logs.add("表删除失败: " + tableName + ", 错误: " + e.getMessage());
            logger.error("表删除失败: {}", tableName, e);
        }
    }
}
